<?php
function get_today() {
        if ($_SESSION['date_style'] == 'YYYYMMDD') $today = date('Ymd');
        if ($_SESSION['date_style'] == 'DDMMYYYY') $today = date('dmY');
        if ($_SESSION['date_style'] == 'MMDDYYYY') $today = date('mdY');
        return $today;
}

function get_time_from_time_slot($slot) {

        $hour = $_SESSION['FromHour'];
        $minutes = $_SESSION['FromMinute'];

        for ($i = 0; $i <= $slot ; $i++) {
                $time_text = sprintf('%02d', $hour) . ":" . sprintf('%02d', $minutes);

                $minutes += $_SESSION['TimeSlice'];
                if ($minutes >= 60) {
                        $minutes -= 60;
                        $hour += 1;
                }
        }

        return $time_text;
}

function get_time_slot_from_time($time_text, $time_slice=0) {
        if ($time_slice == 0) $time_slice = $_SESSION['TimeSlice'];

        // assume the $time_text is 5 character format hh:mm
        $hour = substr($time_text,0,2);
        $minutes = substr($time_text,3,2);

        $number_of_hours = $hour - $_SESSION['FromHour'];
        $number_of_minutes = $minutes - $_SESSION['FromMinute'];

        $total_minutes = $number_of_hours * 60 + $number_of_minutes;

        $slot = $total_minutes / $time_slice ;

        return $slot;
}

function get_client_list() {
        $sql = "SELECT * FROM clientmaster WHERE Enable = 1 ";
        $sql .= " ORDER BY ClientID";

        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $client_list[$i]['ClientID'] = $sql_data['ClientID'];
                $client_list[$i]['ClientName'] = $sql_data['ClientName'];
                $i ++ ;
        }
        return $client_list;
}

function get_staff_list() {
        $sql = "SELECT * FROM staffmaster WHERE Enable = 1 ";
        $sql .= " ORDER BY StaffID";

        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $staff_list[$i]['StaffID'] = $sql_data['StaffID'];
                $staff_list[$i]['StaffName'] = $sql_data['StaffName'];
                $staff_list[$i]['StaffGrade'] = $sql_data['StaffGrade'];
                $i ++ ;
        }
        return $staff_list;
}

function get_staff_grade($StaffID) {
        $sql = "SELECT StaffGrade FROM staffmaster WHERE StaffID = '$StaffID'";
        $sql_result = sql_execute($sql);

        $sql_data = mysql_fetch_array($sql_result);

        return $sql_data['StaffGrade'];
}

function get_charge_type($ChargeID) {
         $sql = "SELECT ChargeType FROM chargetypemaster WHERE " .
                   "ChargeID = '$ChargeID'";
        $sql_result = sql_execute($sql);
        $sql_data = mysql_fetch_array($sql_result);
        return $sql_data['ChargeType'];
}

function get_charge_rate($ChargeID, $CompanyID, $ClientID, $JobID, $date, &$TaxRate) {
        // step 1 : find the most specific record
        $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                   "ChargeID = '$ChargeID' AND " .
                   "CompanyID = '$CompanyID' AND " .
                   "ClientID = '$ClientID' AND " .
                   "JobID = '$JobID' AND " .
                   "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
        $sql_result = sql_execute($sql);
        $sql_data = mysql_fetch_array($sql_result);

        if (! $sql_data['ChargeRate']) {
                // step 2 : find charge rate without job ID
                $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                           "ChargeID = '$ChargeID' AND " .
                           "CompanyID = '$CompanyID' AND " .
                           "ClientID = '$ClientID' AND " .
                           "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
                $sql_result = sql_execute($sql);
                $sql_data = mysql_fetch_array($sql_result);

                if (! $sql_data['ChargeRate']) {
                        // step 3 : find charge rate without client ID
                        $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                                   "ChargeID = '$ChargeID' AND " .
                                   "CompanyID = '$CompanyID' AND " .
                                   "JobID = '$JobID' AND " .
                                   "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
                        $sql_result = sql_execute($sql);
                        $sql_data = mysql_fetch_array($sql_result);

                        if (! $sql_data['ChargeRate']) {
                                // step 4 : find charge rate with charge ID and company ID only
                                $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                                           "ChargeID = '$ChargeID' AND " .
                                           "CompanyID = '$CompanyID' AND " .
                                           "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
                                $sql_result = sql_execute($sql);
                                $sql_data = mysql_fetch_array($sql_result);

                                if (! $sql_data['ChargeRate']) {
                                        // step 5. find charge rate with job ID and charge ID only
                                        $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                                                   "ChargeID = '$ChargeID' AND " .
                                                      "JobID = '$JobID' AND " .
                                                      "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
                                        $sql_result = sql_execute($sql);
                                        $sql_data = mysql_fetch_array($sql_result);

                                        if (! $sql_data['ChargeRate']) {
                                                // step 6 : find charge rate without company ID
                                                $sql = "SELECT ChargeRate,TaxRate FROM chargeratemaster WHERE " .
                                                           "ChargeID = '$ChargeID' AND " .
                                                           "EffectiveDate <= '$date' ORDER BY EffectiveDate DESC LIMIT 1";
                                                $sql_result = sql_execute($sql);
                                                $sql_data = mysql_fetch_array($sql_result);
                                        }
                                }
                        }
                }
        }

        if (! $sql_data['ChargeRate']) return 0;
        else {
          $TaxRate = $sql_data['TaxRate'];
          return $sql_data['ChargeRate'];
        }
}

// Modified 02.12.2005: $type can now be 'exp', which means all rate of type
// expense, currently E (standard forfait expense) and F (footlist expense)

function get_charge_list($type='ALL') {
        $sql = "SELECT * FROM chargetypemaster WHERE Enable = 1 ";
        if ($type != 'ALL') {
            if ($type == 'exp') {
                $sql .= "AND (ChargeType = 'E' OR ChargeType = 'F')";
            }
            else {
                $sql .= "AND ChargeType = '$type'";
            }
        }
        $sql .= " ORDER BY ChargeDescription";

        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $charge_list[$i]['ChargeID'] = $sql_data['ChargeID'];
                $charge_list[$i]['ChargeType'] = $sql_data['ChargeType'];
                $charge_list[$i]['ChargeDescription'] = $sql_data['ChargeDescription'];
                $i ++ ;
        }
        return $charge_list;
}

function get_job_list() {
        $sql = "SELECT * FROM jobmaster WHERE Enable = 1 ";
        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $job[$i]['JobID'] = $sql_data['JobID'];
                $job[$i]['JobName'] = $sql_data['JobName'];
                $i ++;
        }
        return $job;
}

function get_company_list() {
        $sql = "SELECT CompanyID, CompanyName FROM companymaster";
        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $company[$i]['CompanyID'] = $sql_data['CompanyID'];
                $company[$i]['CompanyName'] = $sql_data['CompanyName'];
                $i ++;
        }
        return $company;
}

function get_language_list() {
        $sql = "SELECT LanguageID, LanguageFile FROM languagemaster WHERE Enable = 1 ";
        $sql_result = sql_execute($sql);

        $i = 1;
        while ($sql_data = mysql_fetch_array($sql_result)) {
                $company[$i]['LanguageID'] = $sql_data['LanguageID'];
                $company[$i]['LanguageFile'] = $sql_data['LanguageFile'];
                $i ++;
        }
        return $company;
}

function validate_client($client) {
//validate existence of client ID from user input
        $sql = "SELECT count(*) FROM clientmaster WHERE ClientID = '$client'";
        $sql_result = sql_execute($sql);
        $sql_data = mysql_fetch_array($sql_result);
        if ($sql_data[0] < 1) return 0;
        else return 1;
}

function validate_date($date, $convert=0) {
// validate a date from html input
// 2nd optional parameter is either 1 or 0. '0' just validate; '1' return date in mysql format YYYY-MM-DD
        global $century;

        // validate date length
        if (strlen($date) == 8) {
                if ($_SESSION['DateStyle'] == 'YYYYDDMM') {
                        $year = substr($date,0,4);
                        $month = substr($date,4,2);
                        $day = substr($date,6,2);
                }
                elseif ($_SESSION['DateStyle'] == 'DDMMYYYY') {
                        $year = substr($date,4,4);
                        $month = substr($date,2,2);
                        $day = substr($date,0,2);
                }
                else { //($date_style == 'MMDDYYYY')
                        $year = substr($date,4,4);
                        $month = substr($date,0,2);
                        $day = substr($date,2,2);
                }
                if (checkdate($month, $day, $year)) {
                        if ($convert == 1) return $year."-".$month."-".$day ;
                        else return 1;
                }
                else return 0;
        }
        elseif (strlen($date) == 6) {
                if ($_SESSION['DateStyle'] == 'YYYYDDMM') {
                        $year = $century . str_pad(substr($date,0,2), 2, '0', STR_PAD_LEFT);
                        $month = substr($date,2,2);
                        $day = substr($date,4,2);
                }
                elseif ($_SESSION['DateStyle'] == 'DDMMYYYY') {
                        $year = $century . str_pad(substr($date,4,2), 2, '0', STR_PAD_LEFT);
                        $month = substr($date,2,2);
                        $day = substr($date,0,2);
                }
                else { //($date_style == 'MMDDYYYY')
                        $year = $century . str_pad(substr($date,4,2), 2, '0', STR_PAD_LEFT);
                        $month = substr($date,0,2);
                        $day = substr($date,2,2);
                }
                if (checkdate($month, $day, $year)) {
                        if ($convert == 1) return $year."-".$month."-".$day ;
                        else return 1;
                }
                else return 0;
        }
        else return 0; //incorrect date length
}

function validate_number($number, $decimal=2) {
// validate an html input string is a numeric field with max decimal places
        // remove comma of the string
        $number = str_replace(",", "", $number);

        if (is_numeric($number) && round($number, $decimal) == $number) return 1;
        else return 0;
}

function get_next_number($document_company) {
// obtain next number for a particular company

        // otain the next number from table - sequential_number
        $sql = "SELECT SequentialNumber FROM sequentialnumber
                        WHERE CompanyID = '$document_company'";

        $sql_result = sql_execute($sql);

        $sql_data = mysql_fetch_array($sql_result);

        // when no next number found
        if (! $sql_data) {
                // create one in sequential_number_master
                $sql = "INSERT INTO sequentialnumber (CompanyID, SequentialNumber) VALUES ('$document_company', 2)";
                $sql_result = sql_execute($sql);
                return 1;
        }
        else {
                // when next number found
                $next_number = $sql_data['SequentialNumber'] + 1;

                // update next number in table - sequential_master
                $sql = "UPDATE sequentialnumber SET sequentialnumber = $next_number WHERE CompanyID = '$document_company'";
                $sql_result = sql_execute($sql);

                return $sql_data['SequentialNumber'];
        }
}

function convert_mysql_date($date) {
// convert date stored in mysql database (YYYY-MM-DD) into user default format

        if ($_SESSION['DateStyle'] == 'YYYYMMDD') {
                $user_date = substr($date,0,4) . substr($date,5,2) . substr($date,8,2);
        }
        elseif ($_SESSION['DateStyle'] == 'MMDDYYYY') {
                $user_date = substr($date,5,2) . substr($date,8,2) . substr($date,0,4);
        }
        else { // $_SESSION['valid_user']['date_style'] == 'DDMMYYYY'
                $user_date = substr($date,8,2) . substr($date,5,2) . substr($date,0,4);
        }

        return $user_date;
}

function convert_mysql_date4output($date) {
         // convert date stored in mysql database (YYYY-MM-DD) into user default format plus - separetor
         $user_date = convert_mysql_date($date);
         $user_date = substr($user_date,0,2) . "-" . substr($user_date,2,2) . "-" . substr($user_date,4,4);
         return $user_date;
}

function convert_unix_to_mysql_date($unix_timestamp) {
// convert unix timestamp to text in YYYYMMDD
        $temp = getdate($unix_timestamp);
        $year = $temp['year'];
        $month = sprintf('%02d', $temp['mon']);
        $day = sprintf('%02d', $temp['mday']);

        return $year . "-" . $month . "-" . $day;
}

function convert_user_to_mysql_date($user_date) {
        return substr($user_date,4,4) . "-" . substr($user_date,2,2) . "-" . substr($user_date,0,2);
}

function open_period_check($date) {
        // $date parameter should be YYYY-MM-DD format
        $sql = "SELECT * FROM openperiod";
        $sql_result = sql_execute($sql);
        $sql_data = mysql_fetch_array($sql_result);

        if (($sql_data['ToDate'] < $date) || ($sql_data['FromDate'] > $date))
                return 0;
        else
                return 1;
}

function get_charge_desc($ChargeID) {
	$ChargeData = mysql_fetch_array(sql_execute("select * from chargetypemaster where ChargeID='$ChargeID'"));
	return $ChargeData["ChargeDescription"];

}

function get_project_list($ClientID) {
	$sql = "SELECT ProjectID,ProjectName FROM projectmaster WHERE ClientID LIKE '".$ClientID."'";
    $sql_result = sql_execute($sql);
    $sql_data = mysql_fetch_array($sql_result);
    return $sql_data;
}

?>
